
package org.ihale.database;


import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.ihale.Ihale;
import org.ihale.tedarikci.Tedarikci;
import org.ihale.satinalmaci.SatinAlmaci;


/**
 *
 * @author Emre
 */
public class Database {

     private Connection connection = null;

     private final String driver = "com.mysql.jdbc.Driver";
     private  final String connectionString = "jdbc:mysql://localhost:3306/mydb";
     private  final String user = "root";
     private  final String password = "23072348290";

    public  void connect() throws ClassNotFoundException, SQLException {
        Class.forName(driver);
       connection = DriverManager.getConnection(connectionString, user, password);
        System.out.println("Jdbc Mysql Connection String :");
        System.out.println(connection);


    }
      /**
     * Disconnection method for disconnecting the database
     * @throws SQLException Disconnect sql exception
     */
    
    public void disconnect() throws SQLException{
        if ((this.connection!=null)) {
            if (!this.connection.isClosed()) {
                this.connection.close();
            }
        }
    }
    
    public List<Ihale> listeleIhale(){
        List<Ihale> ihaleler = new LinkedList<Ihale>();
        try {
            String query= "Select * from ihale";
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            Ihale ihale;    
            java.util.Date baslamaTarihi;
            java.util.Date bitisTarihi;
            String baslangicSaati;
            String bitisSaati;
            SatinAlmaci satinAlmaci = new SatinAlmaci();
            while(rs.next()){
            Timestamp tba = rs.getTimestamp("baslama_tarihi");    
            //baslamaTarihi = new java.util.Date(tba.getYear(),tba.getMonth(),tba.getDate());
             baslamaTarihi = new java.util.Date(tba.getTime());
            Timestamp tbi = rs.getTimestamp("bitis_tarihi");    
           // bitisTarihi = new java.util.Date(tbi.getYear(),tbi.getMonth(),tbi.getDate());
            bitisTarihi = new java.util.Date(tbi.getTime());
            baslangicSaati = Integer.toString(tba.getHours())+":"+ Integer.toString(tba.getMinutes());
            bitisSaati = Integer.toString(tbi.getHours())+":"+ Integer.toString(tbi.getMinutes());
            satinAlmaci.setId(rs.getInt("sat_almaci_id"));
            satinAlmaci = getSatinAlmaci(satinAlmaci);    
            ihale = new Ihale(rs.getInt("idihale"), rs.getString("aciklama"), baslamaTarihi, bitisTarihi, satinAlmaci, rs.getString("ihale_adi"), baslangicSaati, bitisSaati);
            ihaleler.add(ihale);
            }
            
            
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ihaleler;
    
    }
    
    public void ekleIhale(Ihale ihale) throws SQLException{
    String[] tmp = new String[2];
            tmp = ihale.getBaslangicSaati().split(":");
            String[] tmp2 = new String[2];
            tmp2 = ihale.getBitisSaati().split(":");    
        
    String query = "INSERT INTO ihale(ihale_adi,baslama_tarihi,bitis_tarihi,sat_almaci_id,aciklama) VALUES (?,?,?,?,?)";
        PreparedStatement statement = connection.prepareStatement(query);
        java.sql.Timestamp baslangic= new Timestamp(ihale.getBaslmaTarihi().getYear(),ihale.getBaslmaTarihi().getMonth(), ihale.getBaslmaTarihi().getDate(), Integer.parseInt(tmp[0]), Integer.parseInt(tmp[1]), 0, 0);
         java.sql.Timestamp bitis= new Timestamp(ihale.getBitisTarihi().getYear(),ihale.getBitisTarihi().getMonth(), ihale.getBitisTarihi().getDate(), Integer.parseInt(tmp2[0]), Integer.parseInt(tmp2[1]), 0, 0); 
    //java.sql.Date baslangic = new Date(ihale.getBaslmaTarihi().getTime());
    //java.sql.Date bitis = new Date(ihale.getBitisTarihi().getTime());
    statement.setString(1, ihale.getAd());
    statement.setTimestamp(2, baslangic);
    statement.setTimestamp(3, bitis);
    statement.setInt(4, ihale.getSatinAlmaci().getId());
    statement.setString(5, ihale.getAciklama());
    statement.execute();
    
    
    }
    
    public void silIhale(Ihale ihale) throws SQLException{
    String query = "DELETE FROM ihale WHERE idihale =? ";
    PreparedStatement stmet = (PreparedStatement) connection.prepareStatement(query);
    stmet.setInt(1,ihale.getId() );
    stmet.executeUpdate();
    
    }
    
    public void guncelleIhale(Ihale ihale){
         String[] tmp = new String[2];
            tmp = ihale.getBaslangicSaati().split(":");
            String[] tmp2 = new String[2];
            tmp2 = ihale.getBitisSaati().split(":");    
        
   
        java.sql.Timestamp baslangic= new Timestamp(ihale.getBaslmaTarihi().getYear(),ihale.getBaslmaTarihi().getMonth(), ihale.getBaslmaTarihi().getDate(), Integer.parseInt(tmp[0]), Integer.parseInt(tmp[1]), 0, 0);
         java.sql.Timestamp bitis= new Timestamp(ihale.getBitisTarihi().getYear(),ihale.getBitisTarihi().getMonth(), ihale.getBitisTarihi().getDate(), Integer.parseInt(tmp2[0]), Integer.parseInt(tmp2[1]), 0, 0); 
        try {
            String query = "UPDATE ihale SET ihale_adi = ?,baslama_tarihi =?,bitis_tarihi = ? , sat_almaci_id = ? , aciklama =? where idihale =?";
            PreparedStatement stmt = connection.prepareStatement(query);
       
            stmt.setString(1, ihale.getAd());
            stmt.setTimestamp(2, baslangic);
            stmt.setTimestamp(3, bitis);
            stmt.setInt(4, ihale.getSatinAlmaci().getId());
            stmt.setString(5, ihale.getAciklama());
            stmt.setInt(6, ihale.getId());
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
    
    
    }
    
    public SatinAlmaci getSatinAlmaci(SatinAlmaci satinAlmaci){
        SatinAlmaci satinAlmaci2 = new SatinAlmaci();
        try {
            
            String query = "Select * from satinalmaci where idsatinalmaci = ?";
            PreparedStatement stmt = connection.prepareStatement(query);
            stmt.setInt(1, satinAlmaci.getId());
            ResultSet rs= stmt.executeQuery();
            if(rs.next()){
               satinAlmaci2 = new SatinAlmaci(rs.getInt("idsatinalmaci"),rs.getString("sirket_adi"),rs.getString("adres_tel"),rs.getString("websitesi"),rs.getString("ilgili_kisi"),rs.getString("sifre"),rs.getString("kullanici_adi"),rs.getString("eposta")); 
               
            }
               
        
        
        
        
        
           
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
         return satinAlmaci2;
    }
    
    public Map<String,String> getSatinAlmacilar(){
        Map<String,String> satinAlmaciMap = new HashMap<String,String>();
        
        try {
            String query ="Select * from satinalmaci";
            Statement stmt = connection.createStatement();
            ResultSet rs =  stmt.executeQuery(query);
            SatinAlmaci satinAlmaci;
            while(rs.next()){
                //int id, String sirketAdi, String adres, String webSite, String ilgiliKisi, String sifre, String kullaniciAdi, String eposta)
               satinAlmaci = new SatinAlmaci(rs.getInt("idsatinalmaci"),rs.getString("sirket_adi"),rs.getString("adres_tel"),rs.getString("websitesi"),rs.getString("ilgili_kisi"),rs.getString("sifre"),rs.getString("kullanici_adi"),rs.getString("eposta")); 
              satinAlmaciMap.put(satinAlmaci.getSirketAdi(), Integer.toString(satinAlmaci.getId()));
               
            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
    
        return satinAlmaciMap;
    
    }

    public boolean adminLogin(String username, String password) {
       return true;
    }

    /********satin almaci fonksiyonları
     * Author Mehmet Sinan
     * 
     */
    public boolean ekleSatinalmaci(SatinAlmaci satinAlmaci)
    {
    String query = "INSERT INTO satinalmaci(sirket_adi,adres_tel,websitesi,ilgili_kisi,sifre,kullanici_adi,eposta) VALUES (?,?,?,?,?,?,?)";
   try {
    PreparedStatement statement = connection.prepareStatement(query);
    
    statement.setString(1, satinAlmaci.getSirketAdi());
    statement.setString(2, satinAlmaci.getAdres());
    statement.setString(3, satinAlmaci.getWebSite());
    statement.setString(4, satinAlmaci.getIlgiliKisi());
    statement.setString(5, satinAlmaci.getSifre());
    statement.setString(6, satinAlmaci.getKullaniciAdi());
    statement.setString(7, satinAlmaci.getEposta());
    statement.executeUpdate();
    }catch(SQLException e)
    {
        return false;
    }
    return true;
    }

public List<SatinAlmaci> ListeSatinalmaci() throws SQLException {
     String query = "SELECT idsatinalmaci, sirket_adi,adres_tel,websitesi,ilgili_kisi,kullanici_adi,eposta FROM  satinalmaci";
     List<SatinAlmaci> satinalmaciListesi = new ArrayList<SatinAlmaci>();
     
     try {
     Statement statement = this.connection.createStatement();
     ResultSet result = statement.executeQuery(query);
        while (result.next()) {
            SatinAlmaci satinAlmaci = new SatinAlmaci();
            satinAlmaci.setId(result.getInt("idsatinalmaci"));
            satinAlmaci.setSirketAdi(result.getString("sirket_adi"));
            satinAlmaci.setAdres(result.getString("adres_tel"));
            satinAlmaci.setWebSite(result.getString("websitesi"));
            satinAlmaci.setIlgiliKisi(result.getString("ilgili_kisi"));
            satinAlmaci.setKullaniciAdi(result.getString("kullanici_adi"));
            satinAlmaci.setEposta(result.getString("eposta"));
            satinalmaciListesi.add(satinAlmaci);
        }
        return satinalmaciListesi;
     }catch(SQLException e)
    {
        return satinalmaciListesi;
    }


    }

    public SatinAlmaci getirSatinalmaci(String satinalmaci_id) {
         String query = "SELECT idsatinalmaci, sirket_adi,adres_tel,websitesi,ilgili_kisi,kullanici_adi,eposta FROM  satinalmaci where (idsatinalmaci=?)";
         SatinAlmaci satinAlmaci = new SatinAlmaci();
     try {
           PreparedStatement commit = this.connection.prepareStatement(query);
            commit.setString(1, satinalmaci_id);
            ResultSet result = commit.executeQuery();
            satinAlmaci.setId(result.getInt("idsatinalmaci"));
            satinAlmaci.setSirketAdi(result.getString("sirket_adi"));
            satinAlmaci.setAdres(result.getString("adres_tel"));
            satinAlmaci.setWebSite(result.getString("websitesi"));
            satinAlmaci.setIlgiliKisi(result.getString("ilgili_kisi"));
            satinAlmaci.setKullaniciAdi(result.getString("kullanici_adi"));
            satinAlmaci.setEposta(result.getString("eposta"));
           return satinAlmaci;
        }
        catch(SQLException e)
        {
            return satinAlmaci;
        }
     }
    
    public boolean silSatinalmaci(SatinAlmaci satinalmaci) {
         String query = "Delete from satinalmaci where (idsatinalmaci=?)";
    try {
           PreparedStatement stmet = (PreparedStatement) connection.prepareStatement(query);
           stmet.setInt(1,satinalmaci.getId() );
           stmet.executeUpdate();
            return true;
        }
        catch(SQLException e)
        {
            return false;
        }
     }
    
    
     public boolean guncelleSatinalmaci(SatinAlmaci satinalmaci)
     {
        try {
            String query = "UPDATE satinalmaci SET  sirket_adi=?,adres_tel=?,websitesi=?,ilgili_kisi=? where idsatinalmaci =?";
            PreparedStatement stmt = connection.prepareStatement(query);
            stmt.setString(1, satinalmaci.getSirketAdi());
            stmt.setString(2, satinalmaci.getAdres());
            stmt.setString(3, satinalmaci.getWebSite());
            stmt.setString(4, satinalmaci.getIlgiliKisi());
            stmt.setInt(5, satinalmaci.getId());
            stmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    
     }




 /********tedarikçi fonksiyonları
     * Author Mehmet Sinan
     * 
     */
    public boolean ekleTedarikci(Tedarikci tedarikci)
    {
    String query = "INSERT INTO tedarikci(sirket_adi,adres_tel,websitesi,ilgili_kisi,sifre,kullanici_adi,eposta) VALUES (?,?,?,?,?,?,?)";
   try {
    PreparedStatement statement = connection.prepareStatement(query);
    
    statement.setString(1, tedarikci.getSirketAdi());
    statement.setString(2, tedarikci.getAdres());
    statement.setString(3, tedarikci.getWebSite());
    statement.setString(4, tedarikci.getIlgiliKisi());
    statement.setString(5, tedarikci.getSifre());
    statement.setString(6, tedarikci.getKullaniciAdi());
    statement.setString(7, tedarikci.getEposta());
    statement.executeUpdate();
    }catch(SQLException e)
    {
        return false;
    }
    return true;
    }

public List<Tedarikci> ListeTedarikci() throws SQLException {
     String query = "SELECT idtedarikci, sirket_adi,adres_tel,websitesi,ilgili_kisi,kullanici_adi,eposta FROM  tedarikci";
     List<Tedarikci> TedarikciListesi = new ArrayList<Tedarikci>();
     
     try {
     Statement statement = this.connection.createStatement();
     ResultSet result = statement.executeQuery(query);
        while (result.next()) {
            Tedarikci tedarikci = new Tedarikci();
            tedarikci.setId(result.getInt("idtedarikci"));
            tedarikci.setSirketAdi(result.getString("sirket_adi"));
            tedarikci.setAdres(result.getString("adres_tel"));
            tedarikci.setWebSite(result.getString("websitesi"));
            tedarikci.setIlgiliKisi(result.getString("ilgili_kisi"));
            tedarikci.setKullaniciAdi(result.getString("kullanici_adi"));
            tedarikci.setEposta(result.getString("eposta"));
            TedarikciListesi.add(tedarikci);
        }
        return TedarikciListesi;
     }catch(SQLException e)
    {
        return TedarikciListesi;
    }


    }

    public Tedarikci getirTedarikci(String tedarikci_id) {
         String query = "SELECT idtedarikci, sirket_adi,adres_tel,websitesi,ilgili_kisi,kullanici_adi,eposta FROM  tedarikci where (idtedarikci=?)";
         Tedarikci tedarikci = new Tedarikci();
     try {
           PreparedStatement commit = this.connection.prepareStatement(query);
            commit.setString(1, tedarikci_id);
            ResultSet result = commit.executeQuery();
            tedarikci.setId(result.getInt("idtedarikci"));
            tedarikci.setSirketAdi(result.getString("sirket_adi"));
            tedarikci.setAdres(result.getString("adres_tel"));
            tedarikci.setWebSite(result.getString("websitesi"));
            tedarikci.setIlgiliKisi(result.getString("ilgili_kisi"));
            tedarikci.setKullaniciAdi(result.getString("kullanici_adi"));
            tedarikci.setEposta(result.getString("eposta"));
           return tedarikci;
        }
        catch(SQLException e)
        {
            return tedarikci;
        }
     }
    
    public boolean silTedarikci(Tedarikci tedarikci) {
         String query = "Delete from tedarikci where (idtedarikci=?)";
    try {
           PreparedStatement stmet = (PreparedStatement) connection.prepareStatement(query);
           stmet.setInt(1,tedarikci.getId() );
           stmet.executeUpdate();
            return true;
        }
        catch(SQLException e)
        {
            return false;
        }
     }
    
    
     public boolean guncelleTedarikci(Tedarikci tedarikci)
     {
        try {
            String query = "UPDATE tedarikci SET  sirket_adi=?,adres_tel=?,websitesi=?,ilgili_kisi=? where idtedarikci =?";
            PreparedStatement stmt = connection.prepareStatement(query);
            stmt.setString(1, tedarikci.getSirketAdi());
            stmt.setString(2, tedarikci.getAdres());
            stmt.setString(3, tedarikci.getWebSite());
            stmt.setString(4, tedarikci.getIlgiliKisi());
            stmt.setInt(5, tedarikci.getId());
            stmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    
     }



}

